import frappe
from pypika.terms import Tuple
from frappe.query_builder.functions import IfNull, Sum

def get_item_code_map(item_code_list, company):
    item_list = frappe.get_all("Item", 
            filters = {'name':('in', item_code_list),
                        'company':company},
            fields =['name',
                     'min_order_qty',
                     'min_pack_qty',
                     '`tabItem Default`.default_warehouse',
                     '`tabItem Default`.default_supplier'])
    item_map = {item.name:(item.min_order_qty,item.min_pack_qty,item.default_warehouse, item.default_supplier) 
                for item in item_list}
    return item_map

def get_whole_number_uom_list(uom_list = []):
    filters = {"must_be_whole_number": 1}
    if uom_list:
        filters['name'] = ("in", uom_list)
    return set(frappe.get_all("UOM",
        filters =filters,
        pluck = "name"
        ))

def get_bin_qty_map(company, item_code_list=[]):
    bin = frappe.qb.DocType("Bin")
    wh = frappe.qb.DocType("Warehouse")
    subquery = frappe.qb.from_(wh).select(wh.name).where(wh.company == company)
    query = (
        frappe.qb.from_(bin)
        .select(
            bin.item_code,
            bin.warehouse,
            IfNull(Sum(bin.projected_qty), 0).as_("projected_qty"),
            IfNull(Sum(bin.actual_qty), 0).as_("actual_qty"),
            IfNull(Sum(bin.ordered_qty), 0).as_("ordered_qty"),
            IfNull(Sum(bin.reserved_qty_for_production), 0).as_("reserved_qty_for_production"),
            IfNull(Sum(bin.planned_qty), 0).as_("planned_qty"),
        )
        .where(bin.warehouse.isin(subquery))
        .groupby(bin.item_code, bin.warehouse)
    )
    if item_code_list:
        query.where(bin.item_code.isin(item_code_list))
    data = query.run(as_dict=True)
    bin_qty_map = {(d.item_code,d.warehouse):d for d in data}
    return bin_qty_map

def get_projected_qty_map(item_warehouse_list):
    sql = """select 
                    item_code,
                    warehouse,
                    projected_qty
                from 
                    tabBin
                where
                    (item_code,warehouse) in %s
                """               
    projected_qty_list = frappe.db.sql(sql, (item_warehouse_list,), as_dict = True)
    projected_qty_map = {
            (row.item_code,row.warehouse):row.projected_qty
                for row in projected_qty_list
        }
    return projected_qty_map

def reverse_reserved_qty(po_items, bin_qty_map):
    """销售订单数量已经从预期可用数量中扣除，需消除对预期可用库存影响"""
    for item in po_items:            
        key = (item.get('item_code'), item.get('warehouse'))
        qty_dict = bin_qty_map.get(key)
        if qty_dict:
            qty_dict.projected_qty += item.get('pending_qty',0)

def get_qty(qty = 0, min_order_qty = 0, min_pack_qty = 0):
    """ get min pack based qty """
    if qty <=0:
        qty = 0 
    elif qty > (min_order_qty or 0) and min_pack_qty:
        Quotient = qty / min_pack_qty
        rounded_quotient = frappe.utils.floor(Quotient)
        if Quotient > rounded_quotient:
            qty = (rounded_quotient + 1) * min_pack_qty
    elif qty < (min_order_qty or 0):
        qty = min_order_qty
    return qty

def get_item_warehouse_qty_map(item_warehouse_tuple):
    bin= frappe.qb.DocType('Bin')        
    data = frappe.qb.from_(bin
        ).select(bin.item_code,bin.warehouse,bin.actual_qty,bin.projected_qty
        ).where(Tuple(bin.item_code,bin.warehouse).isin(item_warehouse_tuple)
        ).run(as_dict=1)
    qty_map = {(d.item_code,d.warehouse):d for d in data}
    return qty_map